﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using University.Workshop.Model;

namespace University.Workshop.DataAccess
{
    public static class DataAccessPurchase
    {
        const string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";

        public static DataTable GetPurchaseCode()
        {
            string sqlSentence = "SELECT MAX(CAST(Code AS BigInt)) FROM Purchase";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        public static bool PurchaseDelete(Purchase purchase)
        {
            string sqlSentence = "Delete from Purchase where (Code = @Code AND NitProvider = @CodeProvider AND CodeUser = @CodeUser AND DatePurchase = @DatePurchase AND TotalPurchase = @TotalPricePurchase)";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (purchase == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Code", purchase.Code);
                cmd.Parameters.AddWithValue("@CodeProvider", purchase.ProviderCode);
                cmd.Parameters.AddWithValue("@CodeUser", purchase.UserCode);
                cmd.Parameters.AddWithValue("@DatePurchase", purchase.DatePurchase);
                cmd.Parameters.AddWithValue("@TotalPricePurchase", Convert.ToDouble(purchase.TotalAmount, System.Globalization.CultureInfo.CurrentCulture));
                result = cmd.ExecuteNonQuery();
                return result != 0;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
        public static bool PurchaseRegister(Purchase purchase)
        {
            string sqlSentence = "INSERT INTO Purchase VALUES(@Code,@CodeProvider,@CodeUser,@DatePurchase,@TotalPricePurchase)";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (purchase == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Code", purchase.Code);
                cmd.Parameters.AddWithValue("@CodeProvider", purchase.ProviderCode);
                cmd.Parameters.AddWithValue("@CodeUser", purchase.UserCode);
                cmd.Parameters.AddWithValue("@DatePurchase", purchase.DatePurchase);
                cmd.Parameters.AddWithValue("@TotalPricePurchase", Convert.ToDouble(purchase.TotalAmount, System.Globalization.CultureInfo.CurrentCulture));
                result = cmd.ExecuteNonQuery();
                
                    return result!=0;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
        public static bool PurchaseDetailRegister(PurchaseDetail purchaseDetail)
        {
            string sqlSentence = "INSERT INTO PurchaseDetail VALUES(@CodePurchase,@CodeProduct,@UnitPrice,@Quantity,@TotalPrice)";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (purchaseDetail == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@CodePurchase", purchaseDetail.CodePurchase);
                cmd.Parameters.AddWithValue("@CodeProduct", purchaseDetail.CodeProduct);
                cmd.Parameters.AddWithValue("@UnitPrice", purchaseDetail.UnitPrice);
                cmd.Parameters.AddWithValue("@Quantity", purchaseDetail.Quantity);
                cmd.Parameters.AddWithValue("@TotalPrice", purchaseDetail.TotalPrice);
                result = cmd.ExecuteNonQuery();

                    UpdateStock(purchaseDetail.CodeProduct, purchaseDetail.Quantity, purchaseDetail.UnitPrice);

                return result!=0;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
        public static bool PurchaseDetailDelete(PurchaseDetail purchaseDetail)
        {
            string sqlSentence = "Delete from PurchaseDetail where(CodePurchase=@CodePurchase AND CodProduct=@CodeProduct AND UnitPrice=@UnitPrice AND Quantity=@Quantity AND TotalPrice=@TotalPrice)";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (purchaseDetail == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@CodePurchase", purchaseDetail.CodePurchase);
                cmd.Parameters.AddWithValue("@CodeProduct", purchaseDetail.CodeProduct);
                cmd.Parameters.AddWithValue("@UnitPrice", purchaseDetail.UnitPrice);
                cmd.Parameters.AddWithValue("@Quantity", purchaseDetail.Quantity);
                cmd.Parameters.AddWithValue("@TotalPrice", purchaseDetail.TotalPrice);
                result = cmd.ExecuteNonQuery();
                return (result != 0);
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }
        private static bool UpdateStock(string productCode, int Quantity, double pricePurchase)
        {
            string sqlSentence = "UPDATE Stock SET Quantity=Quantity+@Quantity,PricePurchase=@pricePurchase, PriceSale=(@pricePurchase+(@pricePurchase*0.15))  WHERE CodeProduct=@CodeProduct";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@CodeProduct", productCode);
                cmd.Parameters.AddWithValue("@Quantity", Quantity);
                cmd.Parameters.AddWithValue("@pricePurchase", pricePurchase);
                result = cmd.ExecuteNonQuery();

                    return result!=0;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

    }
}
